using Microsoft.EntityFrameworkCore;
using OrderingSystem.Core.Entities;
using OrderingSystem.Core.Interfaces;
using OrderingSystem.Infrastructure.Data;
using System.Linq;
using Microsoft.Data.SqlClient;

namespace OrderingSystem.Infrastructure.Services
{
    public class DishDiscussionService : IDishDiscussionService
    {
        private readonly ApplicationDbContext _context;

        public DishDiscussionService(ApplicationDbContext context)
        {
            _context = context;
        }

        public async Task<IEnumerable<DishDiscussion>> GetDiscussionsAsync()
        {
            // 使用SQL查询获取讨论记录
            var discussions = await _context.DishDiscussions
                .FromSqlRaw(@"
                    SELECT 
                        d.id, 
                        d.addtime, 
                        d.content, 
                        d.reply, 
                        d.userid, 
                        d.refid,
                        u.yonghuming as yonghuming,
                        c.caipinmingcheng as caipinmingcheng,
                        c.caipinfenlei as caipinfenlei
                    FROM discusscaipinxinxi d 
                    LEFT JOIN yonghu u ON d.userid = u.id
                    LEFT JOIN caipinxinxi c ON d.refid = c.id
                    ORDER BY d.addtime DESC")
                .ToListAsync();

            // 手动处理用户和菜品关联
            foreach (var discussion in discussions)
            {
                // 尝试查找对应的用户
                var user = await _context.Users
                    .FirstOrDefaultAsync(u => u.id == discussion.userid);
                    
                if (user != null)
                {
                    discussion.User = user;
                }

                // 尝试查找对应的菜品
                var dish = await _context.Dishes
                    .FirstOrDefaultAsync(d => d.id == discussion.refid);
                    
                if (dish != null)
                {
                    discussion.Dish = dish;
                }
            }

            return discussions;
        }

        public async Task<IEnumerable<DishDiscussion>> GetDiscussionsFilteredAsync(DishDiscussion discussion)
        {
            // 构建查询条件
            var query = _context.DishDiscussions.AsQueryable();
            
            if (discussion.id > 0)
            {
                query = query.Where(d => d.id == discussion.id);
            }
            
            if (discussion.userid > 0)
            {
                query = query.Where(d => d.userid == discussion.userid);
            }
            
            if (discussion.refid > 0)
            {
                query = query.Where(d => d.refid == discussion.refid);
            }
            
            if (!string.IsNullOrEmpty(discussion.content))
            {
                query = query.Where(d => d.content.Contains(discussion.content));
            }
            
            // 执行查询
            var discussions = await query
                .OrderByDescending(d => d.addtime)
                .ToListAsync();
                
            // 手动处理用户和菜品关联
            foreach (var disc in discussions)
            {
                // 尝试查找对应的用户
                var user = await _context.Users
                    .FirstOrDefaultAsync(u => u.id == disc.userid);
                    
                if (user != null)
                {
                    disc.User = user;
                    disc.yonghuming = user.yonghuming;
                }

                // 尝试查找对应的菜品
                var dish = await _context.Dishes
                    .FirstOrDefaultAsync(d => d.id == disc.refid);
                    
                if (dish != null)
                {
                    disc.Dish = dish;
                    disc.caipinmingcheng = dish.caipinmingcheng;
                    disc.caipinfenlei = dish.caipinfenlei;
                }
            }
            
            return discussions;
        }

        public async Task<DishDiscussion> GetDiscussionByQueryAsync(DishDiscussion discussion)
        {
            // 构建查询条件
            var query = _context.DishDiscussions.AsQueryable();
            
            if (discussion.id > 0)
            {
                query = query.Where(d => d.id == discussion.id);
            }
            
            if (discussion.userid > 0)
            {
                query = query.Where(d => d.userid == discussion.userid);
            }
            
            if (discussion.refid > 0)
            {
                query = query.Where(d => d.refid == discussion.refid);
            }
            
            if (!string.IsNullOrEmpty(discussion.content))
            {
                query = query.Where(d => d.content.Contains(discussion.content));
            }
            
            // 执行查询
            var result = await query.FirstOrDefaultAsync();
            
            if (result != null)
            {
                // 尝试查找对应的用户
                var user = await _context.Users
                    .FirstOrDefaultAsync(u => u.id == result.userid);
                    
                if (user != null)
                {
                    result.User = user;
                    result.yonghuming = user.yonghuming;
                }

                // 尝试查找对应的菜品
                var dish = await _context.Dishes
                    .FirstOrDefaultAsync(d => d.id == result.refid);
                    
                if (dish != null)
                {
                    result.Dish = dish;
                    result.caipinmingcheng = dish.caipinmingcheng;
                    result.caipinfenlei = dish.caipinfenlei;
                }
            }
            
            return result;
        }

        public virtual async Task<IEnumerable<DishDiscussion>> GetDishDiscussionsAsync(string dishName)
        {
            // 首先查找对应名称的菜品
            var dish = await _context.Dishes
                .FirstOrDefaultAsync(d => d.caipinmingcheng == dishName);
            
            if (dish == null)
            {
                return new List<DishDiscussion>();
            }
            
            // 使用SQL查询获取讨论记录
            var sql = @"
                SELECT 
                    d.id, 
                    d.addtime, 
                    d.content, 
                    d.reply, 
                    d.userid, 
                    d.refid,
                    u.yonghuming as yonghuming,
                    @caipinmingcheng as caipinmingcheng,
                    @caipinfenlei as caipinfenlei
                FROM discusscaipinxinxi d 
                LEFT JOIN yonghu u ON d.userid = u.id
                WHERE d.refid = @dishId 
                ORDER BY d.addtime DESC";

            var discussions = await ExecuteFromSqlRaw(sql, 
                new SqlParameter("@caipinmingcheng", dish.caipinmingcheng),
                new SqlParameter("@caipinfenlei", dish.caipinfenlei),
                new SqlParameter("@dishId", dish.id))
                .ToListAsync();

            // 手动处理菜品关联和用户关联
            foreach (var discussion in discussions)
            {
                // 设置菜品信息
                discussion.Dish = dish;
                
                // 设置用户信息
                var user = await _context.Users
                    .FirstOrDefaultAsync(u => u.id == discussion.userid);
                if (user != null)
                {
                    discussion.User = user;
                }
            }

            return discussions;
        }

        public virtual async Task<DishDiscussion> GetDiscussionAsync(long id)
        {
            // 使用SQL查询获取讨论记录
            var sql = @"
                SELECT 
                    d.id, 
                    d.addtime, 
                    d.content, 
                    d.reply, 
                    d.userid, 
                    d.refid,
                    u.yonghuming as yonghuming,
                    c.caipinmingcheng as caipinmingcheng,
                    c.caipinfenlei as caipinfenlei
                FROM discusscaipinxinxi d 
                LEFT JOIN yonghu u ON d.userid = u.id
                LEFT JOIN caipinxinxi c ON d.refid = c.id
                WHERE d.id = @id";

            var discussion = await ExecuteFromSqlRaw(sql, 
                new SqlParameter("@id", id))
                .FirstOrDefaultAsync();

            if (discussion != null)
            {
                // 尝试查找对应的用户
                var user = await _context.Users
                    .FirstOrDefaultAsync(u => u.id == discussion.userid);
                    
                if (user != null)
                {
                    discussion.User = user;
                }

                // 尝试查找对应的菜品
                var dish = await _context.Dishes
                    .FirstOrDefaultAsync(d => d.id == discussion.refid);
                    
                if (dish != null)
                {
                    discussion.Dish = dish;
                }
            }

            return discussion;
        }

        public async Task<DishDiscussion> CreateDiscussionAsync(DishDiscussion discussion)
        {
            discussion.addtime = DateTime.Now;
            _context.DishDiscussions.Add(discussion);
            await _context.SaveChangesAsync();
            
            // 尝试加载关联的用户和菜品信息
            var user = await _context.Users.FindAsync(discussion.userid);
            if (user != null)
            {
                discussion.User = user;
                discussion.yonghuming = user.yonghuming;
            }
            
            var dish = await _context.Dishes.FindAsync(discussion.refid);
            if (dish != null)
            {
                discussion.Dish = dish;
                discussion.caipinmingcheng = dish.caipinmingcheng;
                discussion.caipinfenlei = dish.caipinfenlei;
            }
            
            return discussion;
        }

        public async Task<DishDiscussion> UpdateDiscussionAsync(DishDiscussion discussion)
        {
            var existingDiscussion = await _context.DishDiscussions.FindAsync(discussion.id);
            if (existingDiscussion == null)
            {
                return null;
            }
            
            // 更新字段
            if (!string.IsNullOrEmpty(discussion.content))
            {
                existingDiscussion.content = discussion.content;
            }
            
            if (!string.IsNullOrEmpty(discussion.reply))
            {
                existingDiscussion.reply = discussion.reply;
            }
            
            await _context.SaveChangesAsync();
            
            // 加载关联的用户和菜品信息
            var user = await _context.Users.FindAsync(existingDiscussion.userid);
            if (user != null)
            {
                existingDiscussion.User = user;
                existingDiscussion.yonghuming = user.yonghuming;
            }
            
            var dish = await _context.Dishes.FindAsync(existingDiscussion.refid);
            if (dish != null)
            {
                existingDiscussion.Dish = dish;
                existingDiscussion.caipinmingcheng = dish.caipinmingcheng;
                existingDiscussion.caipinfenlei = dish.caipinfenlei;
            }
            
            return existingDiscussion;
        }

        public async Task<DishDiscussion> ReplyDiscussionAsync(long id, string reply)
        {
            var discussion = await _context.DishDiscussions.FindAsync(id);
            if (discussion == null)
            {
                return null;
            }

            discussion.reply = reply;
            await _context.SaveChangesAsync();
            
            // 加载关联的用户和菜品信息
            var user = await _context.Users.FindAsync(discussion.userid);
            if (user != null)
            {
                discussion.User = user;
                discussion.yonghuming = user.yonghuming;
            }
            
            var dish = await _context.Dishes.FindAsync(discussion.refid);
            if (dish != null)
            {
                discussion.Dish = dish;
                discussion.caipinmingcheng = dish.caipinmingcheng;
                discussion.caipinfenlei = dish.caipinfenlei;
            }
            
            return discussion;
        }

        public async Task DeleteDiscussionAsync(long id)
        {
            var discussion = await _context.DishDiscussions.FindAsync(id);
            if (discussion != null)
            {
                _context.DishDiscussions.Remove(discussion);
                await _context.SaveChangesAsync();
            }
        }

        // 添加一个受保护的虚拟方法，用于在测试中重写
        protected virtual IQueryable<DishDiscussion> ExecuteFromSqlRaw(string sql, params object[] parameters)
        {
            return _context.DishDiscussions.FromSqlRaw(sql, parameters);
        }
    }
} 